﻿using System.Data.Entity;
using EntityFrameworkETL.Interfaces;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EntityFrameworkETL
{
    public class BatchAndExecuteCommands : IExecuteBatchCommands
    {
        public BatchAndExecuteCommands(DbContext dbContext)
        {
            ConnectionString = dbContext.Database.Connection.ConnectionString;
            this.ExecuteNonQuery = new ExecuteNonQuery();
        }

        public BatchAndExecuteCommands(string connectionstring, IExecuteNonQuery executeNonQuery)
        {
            ConnectionString = connectionstring;
            ExecuteNonQuery = executeNonQuery;
        }

        public string ConnectionString { get; set; }
        public IExecuteNonQuery ExecuteNonQuery { get; set; }

        /// <summary>
        /// Perform Commands in batch staying within sql limit of 2100 parameters
        /// </summary>
        /// <param name="constructor"></param>
        /// <param name="commands"></param>
        public void Execute(IEnumerable<DbCommand> commands)
        {
            var sql = new System.Text.StringBuilder();
            var parameters = new List<DbParameter>();

            foreach (DbCommand command in commands)
            {
                if ((parameters.Count + command.Parameters.Count) > 2000)
                {
                    //BatchTable and clear
                    ExecuteNonQuery.Execute(ConnectionString, sql.ToString(), parameters.ToArray());
                    parameters.Clear();
                    sql.Clear();
                }

                var commandSql = command.CommandText;
                List<DbParameter> commandParameters = new List<DbParameter>();
                foreach (DbParameter param in command.Parameters)
                {
                    commandParameters.Add(param);
                }
                foreach (DbParameter param in commandParameters)
                {
                    //fix parameter names
                    var nextParameterName = "@p" + parameters.Count.ToString();
                    commandSql = commandSql.Replace("(" + param.ParameterName, "(" + nextParameterName);
                    commandSql = commandSql.Replace(param.ParameterName + ",", nextParameterName + ",");
                    commandSql = commandSql.Replace(param.ParameterName + ")", nextParameterName + ")");
                    param.ParameterName = nextParameterName;

                    parameters.Add(param);
                    command.Parameters.Remove(param);
                }
                sql.AppendLine(commandSql);
            }

            //BatchTable Batch Query
            ExecuteNonQuery.Execute(ConnectionString, sql.ToString(), parameters.ToArray());
        }
    }
}
